package com.fiberhome.boot.demo.primary.dao;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.fiberhome.boot.demo.primary.domain.Demo;

@Repository
public interface PrimaryRepository extends CrudRepository<Demo, Long> {
	
	/**自定义简单查询
	 * Keyword	Sample	JPQL snippet
	 *And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
	 *Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
	 *Is,Equals	findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
	 *Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
	 *LessThan	findByAgeLessThan	… where x.age < ?1
	 *LessThanEqual	findByAgeLessThanEqual	… where x.age ⇐ ?1
	 *GreaterThan	findByAgeGreaterThan	… where x.age > ?1
	 *GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
	 *After	findByStartDateAfter	… where x.startDate > ?1
	 *Before	findByStartDateBefore	… where x.startDate < ?1
	 *IsNull	findByAgeIsNull	… where x.age is null
	 *IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
	 *Like	findByFirstnameLike	… where x.firstname like ?1
	 *NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
	 *StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
	*EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
	*Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
	*OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
	*Not	findByLastnameNot	… where x.lastname <> ?1
	*In	findByAgeIn(Collection ages)	… where x.age in ?1
	*NotIn	findByAgeNotIn(Collection age)	… where x.age not in ?1
	*TRUE	findByActiveTrue()	… where x.active = true
	*FALSE	findByActiveFalse()	… where x.active = false
	*IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)
	 * @param name
	 * @return
	 */
	List<Demo> findByName(String name);
	
	/**
	 * 自定义SQL
	 * 在SQL的查询方法上面使用@Query注解
	 * 如涉及到删除和修改在需要加上@Modifying
	 * 也可以根据需要添加 @Transactional 对事物的支持，查询超时的设置
	 */
	@Modifying  //注意select * 会报错
	@Transactional
	@Query("update Demo u set u.name = ?2 where u.id = ?1")
	int modifyByIdAndName(long id, String name);
	    
	@Transactional
	@Modifying
	@Query("delete from Demo where id = ?1")
	void deleteById(Long id);
	  
	@Transactional(timeout = 10)
	@Query("select u from Demo u where u.id = ?1")
	 Demo findById(long id);
	
}
